import pymysql
import socket
import datetime
import os
import json
import csv
import numpy as np
from collections import defaultdict


DB_CONFIG = {
    'host': '172.16.30.54',
    'port': 3306,
    'user': 'root',
    'password': 'IPS2025a%',
    'database': 'iip_numerical_algorithm_mini_program',
    'cursorclass': pymysql.cursors.DictCursor
}


def get_item_features():
    """
    统计商品数据特征并保存到CSV文件
    """
    # 连接数据库
    connection = pymysql.connect(**DB_CONFIG)
    try:
        with connection.cursor() as cursor:
            # 查询: 获取商品点击事件
            click_sql = """
            SELECT
                COALESCE(JSON_EXTRACT(extra_param, '$.articleId'), JSON_EXTRACT(extra_param, '$.videoId'), '-1') as item_id,
                event_time
            FROM
                tracking_data
            WHERE
                event_type LIKE '%click%'
                AND extra_param IS NOT NULL
                AND JSON_VALID(extra_param) = 1
                AND (JSON_EXTRACT(extra_param, '$.articleId') IS NOT NULL OR JSON_EXTRACT(extra_param, '$.videoId') IS NOT NULL)
            ORDER BY
                item_id, event_time
            """
            cursor.execute(click_sql)
            click_results = cursor.fetchall()

            # 处理点击事件数据
            item_click_data = defaultdict(list)
            for row in click_results:
                item_id = row['item_id'].strip('"')
                if item_id != '-1':  # 确保商品ID有效
                    item_click_data[item_id].append(row['event_time'])

            # 准备输出目录
            output_dir = '/data/gongzhijia/data/features'
            os.makedirs(output_dir, exist_ok=True)
            output_file = os.path.join(output_dir, 'item_features.csv')

            # 计算不同时间范围内的点击量
            item_features = {}
            today = datetime.datetime.now()
            time_ranges = [3, 7, 14, 21]  # 天

            for item_id, click_times in item_click_data.items():
                item_features[item_id] = {}
                for days in time_ranges:
                    cutoff_time = today - datetime.timedelta(days=days)
                    count = sum(1 for t in click_times if t >= cutoff_time)
                    item_features[item_id][f'item_click_num_{days}d'] = count

            # 计算排名
            for days in time_ranges:
                # 按点击量降序排列
                sorted_items = sorted(item_features.items(), key=lambda x: x[1][f'item_click_num_{days}d'], reverse=True)
                # 分配排名（处理相同点击量的情况）
                rank = 1
                prev_count = None
                for i, (item_id, features) in enumerate(sorted_items):
                    current_count = features[f'item_click_num_{days}d']
                    if prev_count is not None and current_count < prev_count:
                        rank = i + 1
                    item_features[item_id][f'item_click_rank_{days}d'] = rank
                    prev_count = current_count

            # 写入CSV文件
            with open(output_file, 'w', newline='', encoding='utf-8') as csvfile:
                fieldnames = ['item_id']
                for days in time_ranges:
                    fieldnames.append(f'item_click_num_{days}d')
                for days in time_ranges:
                    fieldnames.append(f'item_click_rank_{days}d')

                writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
                writer.writeheader()

                for item_id, features in item_features.items():
                    row_data = {'item_id': item_id}
                    row_data.update(features)
                    writer.writerow(row_data)

            # 打印统计的商品数量
            print(f"共统计了 {len(item_features)} 个商品的数据")
            print(f"商品特征数据已保存至: {output_file}")
            return output_file

    finally:
        connection.close()


if __name__ == '__main__':
    get_item_features()